package com.java1234.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.java1234.model.animal;
import com.java1234.util.StringUtil;

public class animalDao {

	public int add(Connection con,animal animal1)throws Exception{
		String sql="insert into db_book.dbo.t_animal values(?,?,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, animal1.getId());
		pstmt.setString(2, animal1.getAnimalName());
		pstmt.setString(3, animal1.getAnimalType());
		pstmt.setString(4, animal1.getPrice());
		pstmt.setString(5, animal1.getTxt());
		return pstmt.executeUpdate();
	}

	public List<animal> find1(Connection con,animal animal1)throws Exception{
		List<animal> animalList = new ArrayList<animal>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sq = "select* from db_book.dbo. t_animal where ID like ?";
		pstmt = con.prepareStatement(sq);
		pstmt.setString(1, animal1.getId());
		rs=pstmt.executeQuery();
		while(rs.next()) {
			animalList.add(new animal(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5)));
		}
		return animalList;
	}
	public List<animal> find2(Connection con,animal animal1)throws Exception{
		List<animal> animalList = new ArrayList<animal>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sq = "select* from db_book.dbo. t_animal where animalName like ?";
		pstmt = con.prepareStatement(sq);
		pstmt.setString(1, animal1.getAnimalName());
		rs=pstmt.executeQuery();
		while(rs.next()) {
			animalList.add(new animal(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5)));
		}
		return animalList;
	}
	public List<animal> find3(Connection con,animal animal1)throws Exception{
		List<animal> animalList = new ArrayList<animal>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sq = "select* from db_book.dbo. t_animal ";
		pstmt = con.prepareStatement(sq);
		rs=pstmt.executeQuery();
		while(rs.next()) {
			animalList.add(new animal(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5)));
		}
		return animalList;
	}

	public int delete(Connection con,String id)throws Exception{
		String sql="delete from db_book.dbo.t_animal where ID = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, id);
		return pstmt.executeUpdate();
	}

	public int update(Connection con,animal animal1)throws Exception{
		String sql="update db_book.dbo.t_animal set animalName=?,animalType=?,Price=? ,Txt=? where ID=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, animal1.getAnimalName());
		pstmt.setString(2, animal1.getAnimalType());
		pstmt.setString(3, animal1.getPrice());
		pstmt.setString(4, animal1.getTxt());
		pstmt.setString(5, animal1.getId());


		return pstmt.executeUpdate();
	}
}
